﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Jumpton.SFA.Server.Entity;
using Jumpton.Persistent;
using Jumpton.Data;
using System.Data;

namespace Jumpton.SFA.Server.BusinessRule
{
    public class OrderHeaderManagerKong : BusinessLogic
    {
        public ORDER_HEADERS Load(string ORDER_NO)
        {
            return (ORDER_HEADERS)ObjectRow.Load(typeof(ORDER_HEADERS), string.Format("this.ORDER_NO='{0}'", ORDER_NO));
        }

        public ORDER_HEADERS Load(decimal ORDER_ID)
        {
            return (ORDER_HEADERS)ObjectRow.Load(typeof(ORDER_HEADERS), string.Format("this.ID='{0}'", ORDER_ID));
        }


        //public static ObjectTable GetOrderHeadersList(string where)
        //{
        //    return new ObjectTable(typeof(ORDER_HEADERS), String.Format("{0}", where));
        //}

        public static DataSet GetOrderHeadersList(string where)
        {
            string Query = string.Format(@"select OH.ID,OH.ORDER_NO,OH.STATUS,OH.ORDER_DATE,OH.CUSTOMER_ID,C.NAME from order_headers oh inner join customers c on c.id=oh.CUSTOMER_ID where 1=1
{0} ", where);

            return SqlData.OpenSql(Query);
        }
        public static ObjectTable GetOrderList(decimal ORDER_ID)
        {
            return new ObjectTable(typeof(ORDERHEADERVIEW), String.Format("this.ID='{0}'", ORDER_ID));
        }

        public static DataSet SearchCustomer(string strCondition)
        {
            return SqlData.OpenSP("usp_SelectCUSTOMERSsDynamicForOrderVisit", strCondition);
        }

        public static DataSet SearchHistoryCustomer(string strCondition)
        {
            return SqlData.OpenSP("usp_SelectCUSTOMERSsDynamicForOrderHistoryVisit_Kong", strCondition, 0, 5000, 1);
        }

        public static DataSet SearchHistoryCustomer(decimal userId,string strSelect,string strWhere)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(string.Format(@"SELECT {1} FROM THIRDPARTY_COMPANIES TC
                        INNER JOIN THIRD_ORG_MEMBER TOM ON TC.ID = TOM.THIRD_COM_ID
                        INNER JOIN ORGANIZATION O ON O.ID = TOM.ORG_ID
                        INNER JOIN USERS U ON U.ORG_ID = TC.ID
                        WHERE U.TYPE = 2 AND TOM.ACTIVE = 1 AND U.ID = {0} {2}", userId,strSelect, strWhere));
            return SqlData.OpenSql(sb.ToString());
        }

        public static DataSet SearchHistoryCustomer_excel(string strCondition)
        {
            //return SqlData.OpenSP("usp_SelectCUSTOMERSsDynamicForOrderHistoryVisit_excel", strCondition);
            return SqlData.OpenSP("usp_SelectCUSTOMERSsDynamicForOrderHistoryVisit", strCondition, 0, 5000, 1);
        }

        public static DataSet SearchSupplyReport(string whereCondition)
        {
            return SqlData.OpenSP("usp_SelectSupplyReport", whereCondition.ToString());
        }

        public static DataSet SearchChainsDelivery(string whereCondition)
        {
            return SqlData.OpenSP("USP_SELECTORDERPRINT", whereCondition.ToString());
        }

        public static DataSet SearchExportVisitMeasureDetail(string strCondition, decimal domainID)
        {
            DataSet ds = SqlData.OpenSP("sp_ExportExcelVisitMeauserDetail", strCondition, domainID);
            if (ds.Tables.Count == 0)
            {
                return null;
            }
            string FieldName = "";

            //DataTable dt = new DataTable("Meauser");
            //dt.Columns.Add(new DataColumn("DETAILS"));
            //dt.Columns.Add(new DataColumn("FieldName"));

            DataTable data = new DataTable("Data");
            data.Columns.Add(new DataColumn("CODE"));
            data.Columns.Add(new DataColumn("NAME"));
            data.Columns.Add(new DataColumn("PERSONNAME"));
            data.Columns.Add(new DataColumn("STOP_TIME", typeof(DateTime)));
            data.Columns.Add(new DataColumn("START_TIME", typeof(DateTime)));

            for (int i = 0; i < ds.Tables.Count; i++)
            {
                if (ds.Tables[i].Rows.Count > 0)
                {
                    DataRow row = data.NewRow();
                    for (int j = 0; j < ds.Tables[i].Rows.Count; j++)
                    {
                        FieldName = ds.Tables[i].Rows[j]["DETAILS"].ToString();

                        row["CODE"] = ds.Tables[i].Rows[j]["CUSTOMER_CODE"];
                        row["NAME"] = ds.Tables[i].Rows[j]["customer_NAME"];
                        row["PERSONNAME"] = ds.Tables[i].Rows[j]["PERSON_NAME"];
                        row["STOP_TIME"] = ds.Tables[i].Rows[j]["STOP_TIME"];
                        row["START_TIME"] = ds.Tables[i].Rows[j]["START_TIME"];

                        if (FieldName == "")
                        {
                            break;
                        }
                        else
                        {
                            if (!data.Columns.Contains(FieldName))
                            {
                                data.Columns.Add(new DataColumn(FieldName));
                            }
                            row[FieldName] = ds.Tables[i].Rows[j]["VALUE"];
                        }


                        //DataRow row = dt.NewRow();
                        //row[0] = ds.Tables[i].Rows[j]["DETAILS"];
                        //FieldName = "Value" + (j + 1).ToString();
                        ////ds.Tables[i].Columns["VALUE"].ColumnName = FieldName;
                        //row[1] = FieldName;

                        //dt.Rows.Add(row);

                        //data.Columns.Add(new DataColumn(FieldName));

                    }

                    data.Rows.Add(row);
                }


            }
            ds.Tables.Clear();
            ds.Tables.Add(data);
            data.DefaultView.Sort = "STOP_TIME DESC";

            //ds.Tables.Add(dt);
            return ds;
        }

        public void Save(ORDER_HEADERS orderHeader)
        {
            if (orderHeader != null)
            {
                try
                {
                    orderHeader.Save();

                }
                catch (Exception err)
                {
                    message = err.Message; throw err;
                }
            }
        }

        public static string GetNextOrderNumber(decimal userID, DateTime dt)
        {
            object o = SqlData.ExecuteScalarSP("sp_GetCurrentOrderNumber", userID, dt);
            if (o != null)
            {
                return o.ToString().PadLeft(3, '0');
            }
            throw new Exception("订单号生成异常");
        }

        public static ObjectTable GetOrderList(string userID, string updateTime)
        {
            return new ObjectTable(typeof(ORDERHEADERVIEW), String.Format("a.USER_ID='{0}' and a.ORDER_DATE > '{1}' ", userID, updateTime));
        }

        public static DataSet SearchOrderList(decimal domainID, string whereCondition)
        {
            return SqlData.OpenSP("sp_SearchOrderList", domainID, whereCondition);
        }

        public static DataSet SearchHistoryOrderList(decimal domainID, string whereCondition)
        {
            return SqlData.OpenSP("sp_SearchHistoryOrderList", domainID, whereCondition);
        }

        public static void ExportOrderToHistory(string orderHeaderID, string userName, decimal domainID)
        {
            SqlData.ExecuteSP("sp_ExportOrderByOrderHeaderId", orderHeaderID, userName, domainID);
        }

        public static DataSet OrderHistoryDetailReview(string orderNo)
        {
            return SqlData.OpenSP("sp_ReviewOrderHistoryDetailByOrderNo", orderNo);
        }
        public static DataSet SearchSalesOrder(string orgid, string chiansid, string userid, string visitdate, decimal domainID)
        {
            return SqlData.OpenSP("sp_SelectSalesOrder", orgid, chiansid, userid, visitdate, domainID);
        }
        public static DataSet sp_SelectOrdersForExcel(decimal domainID, string where)
        {
            return SqlData.OpenSP("sp_SelectOrdersForExcel", domainID, where);
        }

        /// <summary>
        /// PDA省级经理查看下属业务员的所有订单
        /// </summary>
        /// <param name="orgID"></param>
        /// <returns></returns>
        public static DataSet SearchEmployeeOrders(int orgID, string begin, string end)
        {
            string Query = string.Format(@"SELECT  top 100   CASE OH.STATUS WHEN 0 THEN '已确认' WHEN 1 THEN '未确认' WHEN 2 THEN '部分确认' WHEN 3 THEN '已取消' ELSE '未知' END AS STATUS_NAME,
                       C.NAME, P.NAME AS PERSON_NAME, OH.ORDER_NO, OH.ORDER_DATE, OH.REQ_DELIVERY_DATE, OH.CUSTOMER_ID, OH.EXTENDED_AMOUNT, 
                      OH.TOTAL_AMOUNT, OH.TOTAL_QUANTITY, OH.DISC_RATE, OH.DISC_AMOUNT, OH.STATUS, OH.VISIT_ID, OH.PAY_TERM_ID, OH.TRANSFER_DATE, 
                      OH.BRACKET_ID, OH.TRUCK_CODE, OH.PURCHASE_ORDER, OH.TIME_RANGE_ID, 
                      OH.GROUP_NUMBER, OH.SHIP_FROM_CODE, OH.ADDITIONAL_INFO, OH.USER_ID, OH.TAX_RATE, OH.TAX_AMOUNT, OH.DISC_1, OH.DISC_2, 
                      OH.DISC_3, OH.CANCELLED, OH.SHIP_TO_CODE, OH.END_DELIVERY_DATE, OH.REQUESTED_DELIVERY_TIME, OH.PROMO, OH.ORDERTYPE, 
                      OH.CHAIN_ID, OH.CONFIRMED_DATE, OH.CONFIRM_USER_CODE, OH.ARRIVAL_DATE, OH.ORG_ID, OH.PERSON_ID, OH.SUPPLY_ID, OH.ORDER_FORM, 
                      OH.ORDERBLANK 
                      FROM ORDER_HEADERS AS OH INNER JOIN
                      VISITS AS V ON V.ID = OH.VISIT_ID INNER JOIN
                      CUSTOMERS AS C ON C.ID = V.CUSTOMER_ID INNER JOIN
                      PERSONS AS P ON P.ID = OH.PERSON_ID
                    WHERE OH.ORDER_DATE >= '{0}' AND OH.ORDER_DATE <'{1}' AND   (OH.USER_ID IN
                          (SELECT     ID
                            FROM          USERS
                            WHERE      (ORG_ID IN
                                                       (SELECT     ID
                                                         FROM          DBO.FC_GETORGWITHPARENTID(1, {2}) AS FC_GETORGWITHPARENTID_1))))", begin, end, orgID);

            return SqlData.OpenSql(Query);

        }

        public bool ExistOrderHeader(string ORDER_NO)
        {
            return new ObjectTable(typeof(ORDER_HEADERS), "ORDER_NO in (select distinct ORDER_NO from ORDER_HEADERS where ORDER_NO='" + ORDER_NO + "')").Count > 0 ? true : false;
        }

        public static string NewOrderId(decimal cid)
        {
            string id = "";
            id = DateTime.Now.ToString("yyyyMMddHHmmss") + cid + new Random().Next(0, 1000).ToString();
            if (id.Length >= 30)
                id = id.Substring(0, 29);
            return id;

        }

        public DataTable GetHistoryOrderList(int userId, long orderDate)
        {

            String sql = String.Format(@"SELECT T1.ID,T1.ORDER_NO,T1.ORDER_DATE,T1.REQ_DELIVERY_DATE,T1.CUSTOMER_ID,T1.TOTAL_AMOUNT,T1.TOTAL_QUANTITY,T1.STATUS,
                                        T1.VISIT_ID,T1.ORDERTYPE,T1.CONFIRMED_DATE,T1.TRUCK_CODE,T1.ADDITIONAL_INFO,t2.NAME 
                                        FROM ORDER_HEADERS T1 INNER JOIN CUSTOMERS T2
                                        ON T1.CUSTOMER_ID=T2.ID AND  T1.ORDER_DATE<{1}  
                                        inner join USERS U on u.ID=T1.USER_ID and u.ID={0}
                                        order by T1.ORDER_date desc", userId, orderDate);
            return Jumpton.Data.SqlData.OpenSql(sql).Tables[0];
        }

        public DataTable GetHistoryOrderList(int userId, long orderDate,int pageSize,int currentPage)
        {
        //    @PageSize = 10,
        //@PageCurrent = 3,
        //@UserId = 58,
        //@orderDate = 20121017000000

            return Jumpton.Data.SqlData.OpenSP("usp_PageOrderHistoryList",pageSize,currentPage,userId,orderDate).Tables[0];
        }
    
    }
}
